<?php

namespace app\services\data\stat\ad\normal;

use app\clickhouse\models\Events;

// 当天注册并创角
class StatRoleRegNew extends StatBaseUserEvent
{
    public function getSql()
    {
        $join             = $this->queryJoin('users');
        $event_where      = $this->queryWhere(['time'], 'events');
        $user_where       = $this->queryWhere(['game_id', 'channel', 'game_channel_id', 'create_time', 'device_type'], 'users');
        $plan_where       = $this->queryWhere(['media_id', 'media_docking_id', 'owner', 'plan_group_id', 'plan_id', 'source_type'], 'ad_plan');
        $media_user_where = $this->queryWhere(['daili', 'media_user_id'], 'ad_media_user');
        $event_id         = Events::EVENT_ROLE_REGISTER;

        $sql = "
            SELECT count(DISTINCT events.`role_unique_id`) AS role_reg_new, count(DISTINCT events.`ip`) AS role_reg_ip_new
            FROM users
            JOIN(
                SELECT `#user_id`, role_unique_id, toDate(`#create_time`, '{$this->timezone}') AS cday, ip FROM sdk.events_local AS events
                WHERE {$event_where}
                AND `#event_id` = {$event_id}
            ) AS events
            ON users.`#user_id` = events.`#user_id`
            {$join}
            WHERE toDate(users.`#create_time`) = events.cday
            AND {$user_where}
            AND {$plan_where}
            AND {$media_user_where}
        ";

        return $sql;
    }

    public function getSqlByGroup()
    {
        $join             = $this->queryJoin('users');
        $event_where      = $this->queryWhere(['time'], 'events');
        $user_where       = $this->queryWhere(['game_id', 'channel', 'game_channel_id', 'create_time', 'device_type'], 'users');
        $plan_where       = $this->queryWhere(['media_id', 'media_docking_id', 'owner', 'plan_group_id', 'plan_id', 'source_type'], 'ad_plan');
        $media_user_where = $this->queryWhere(['daili', 'media_user_id'], 'ad_media_user');
        $event_id         = Events::EVENT_ROLE_REGISTER;

        list($select_str, $groupby_str) = $this->queryGroupBy();

        $sql = "
            SELECT {$select_str}, count(DISTINCT events.`role_unique_id`) AS role_reg_new, count(DISTINCT events.`ip`) AS role_reg_ip_new
            FROM users
            JOIN(
                SELECT `#user_id`, `#create_time`, role_unique_id, toDate(`#create_time`, '{$this->timezone}') AS cday, ip FROM sdk.events_local AS events
                WHERE {$event_where}
                AND `#event_id` = {$event_id}
            ) AS events
            ON users.`#user_id` = events.`#user_id`
            {$join}
            WHERE toDate(users.`#create_time`) = events.cday
            AND {$user_where}
            AND {$plan_where}
            AND {$media_user_where}
            GROUP BY {$groupby_str}
        ";

        return $sql;
    }
}
